package com.hotel.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hotel.entity.Reservation;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * 预订Mapper接口
 */
@Mapper
public interface ReservationMapper extends BaseMapper<Reservation> {
    
    /**
     * 获取今日入住预订
     */
    @Select("SELECT * FROM reservation " +
            "WHERE checkin_date = CURDATE() " +
            "AND status IN ('confirmed', 'checkedin') " +
            "ORDER BY arrival_time, create_time")
    List<Reservation> getTodayCheckinReservations();
    
    /**
     * 获取今日退房预订
     */
    @Select("SELECT * FROM reservation " +
            "WHERE checkout_date = CURDATE() " +
            "AND status = 'checkedin' " +
            "ORDER BY departure_time, checkin_time")
    List<Reservation> getTodayCheckoutReservations();
    
    /**
     * 获取预订统计信息
     */
    @Select("SELECT " +
            "COUNT(*) as total_reservations, " +
            "SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_reservations, " +
            "SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) as confirmed_reservations, " +
            "SUM(CASE WHEN status = 'checkedin' THEN 1 ELSE 0 END) as checkedin_reservations, " +
            "SUM(CASE WHEN status = 'checkedout' THEN 1 ELSE 0 END) as checkedout_reservations, " +
            "SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_reservations, " +
            "SUM(total_amount) as total_revenue, " +
            "SUM(paid_amount) as total_paid, " +
            "AVG(total_amount) as avg_amount " +
            "FROM reservation " +
            "WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)")
    Map<String, Object> getReservationStats();
    
    /**
     * 获取预订趋势数据
     */
    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as date, " +
            "COUNT(*) as reservation_count, " +
            "SUM(total_amount) as revenue, " +
            "AVG(total_amount) as avg_amount " +
            "FROM reservation " +
            "WHERE create_time BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d') " +
            "ORDER BY date")
    List<Map<String, Object>> getReservationTrend(@Param("startDate") String startDate, 
                                                   @Param("endDate") String endDate);
    
    /**
     * 获取房间入住率统计
     */
    @Select("SELECT " +
            "COUNT(DISTINCT r.id) as total_rooms, " +
            "COUNT(res.id) as occupied_nights, " +
            "ROUND(COUNT(res.id) * 100.0 / (COUNT(DISTINCT r.id) * DATEDIFF(#{endDate}, #{startDate})), 2) as occupancy_rate, " +
            "SUM(res.total_amount) as total_revenue, " +
            "CASE WHEN COUNT(res.id) > 0 THEN " +
            "  ROUND(SUM(res.total_amount) / COUNT(res.id), 2) " +
            "ELSE 0 END as adr " +
            "FROM room r " +
            "LEFT JOIN reservation res ON r.id = res.room_id " +
            "  AND res.status IN ('checkedin', 'checkedout') " +
            "  AND res.checkin_date BETWEEN #{startDate} AND #{endDate}")
    Map<String, Object> getOccupancyRate(@Param("startDate") String startDate, 
                                         @Param("endDate") String endDate);
    
    /**
     * 获取即将到期的预订
     */
    @Select("SELECT * FROM reservation " +
            "WHERE status = 'confirmed' " +
            "AND checkin_date <= DATE_ADD(NOW(), INTERVAL #{hours} HOUR) " +
            "AND checkin_date >= CURDATE() " +
            "ORDER BY checkin_date, arrival_time")
    List<Reservation> getExpiringReservations(@Param("hours") int hours);
    
    /**
     * 根据客户信息查询预订
     */
    @Select("SELECT * FROM reservation " +
            "WHERE (customer_name LIKE CONCAT('%', #{customerName}, '%') " +
            "OR customer_phone LIKE CONCAT('%', #{customerPhone}, '%')) " +
            "ORDER BY create_time DESC " +
            "LIMIT 20")
    List<Reservation> getReservationsByCustomer(@Param("customerName") String customerName, 
                                                @Param("customerPhone") String customerPhone);
    
    /**
     * 更新预订状态
     */
    @Update("UPDATE reservation SET status = #{status}, update_time = NOW() WHERE id = #{id}")
    int updateReservationStatus(@Param("id") Long id, @Param("status") String status);
    
    /**
     * 取消预订
     */
    @Update("UPDATE reservation SET status = 'cancelled', cancel_reason = #{reason}, " +
            "cancel_time = NOW(), cancel_by = #{cancelBy}, update_time = NOW() " +
            "WHERE id = #{id}")
    int cancelReservation(@Param("id") Long id, @Param("reason") String reason, @Param("cancelBy") String cancelBy);
    
    /**
     * 确认预订
     */
    @Update("UPDATE reservation SET status = 'confirmed', confirm_time = NOW(), " +
            "confirm_by = #{confirmBy}, update_time = NOW() " +
            "WHERE id = #{id}")
    int confirmReservation(@Param("id") Long id, @Param("confirmBy") String confirmBy);
    
    /**
     * 办理入住
     */
    @Update("UPDATE reservation SET status = 'checkedin', room_id = #{roomId}, " +
            "checkin_time = NOW(), checkin_by = #{checkinBy}, update_time = NOW() " +
            "WHERE id = #{id}")
    int checkinReservation(@Param("id") Long id, @Param("roomId") Long roomId, @Param("checkinBy") String checkinBy);
    
    /**
     * 办理退房
     */
    @Update("UPDATE reservation SET status = 'checkedout', checkout_time = NOW(), " +
            "checkout_by = #{checkoutBy}, update_time = NOW() " +
            "WHERE id = #{id}")
    int checkoutReservation(@Param("id") Long id, @Param("checkoutBy") String checkoutBy);
    
    /**
     * 延长住宿
     */
    @Update("UPDATE reservation SET checkout_date = #{newCheckoutDate}, " +
            "extension_count = COALESCE(extension_count, 0) + 1, update_time = NOW() " +
            "WHERE id = #{id}")
    int extendStay(@Param("id") Long id, @Param("newCheckoutDate") String newCheckoutDate);
    
    /**
     * 更换房间
     */
    @Update("UPDATE reservation SET room_id = #{newRoomId}, " +
            "room_change_count = COALESCE(room_change_count, 0) + 1, update_time = NOW() " +
            "WHERE id = #{id}")
    int changeRoom(@Param("id") Long id, @Param("newRoomId") Long newRoomId);
    
    /**
     * 获取预订来源统计
     */
    @Select("SELECT source, COUNT(*) as count, SUM(total_amount) as revenue " +
            "FROM reservation " +
            "WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) " +
            "GROUP BY source " +
            "ORDER BY count DESC")
    List<Map<String, Object>> getReservationSourceStats();
    
    /**
     * 获取客户预订统计
     */
    @Select("SELECT customer_name, customer_phone, " +
            "COUNT(*) as reservation_count, " +
            "SUM(total_amount) as total_spent, " +
            "MAX(create_time) as last_reservation_time " +
            "FROM reservation " +
            "WHERE status != 'cancelled' " +
            "GROUP BY customer_name, customer_phone " +
            "ORDER BY reservation_count DESC, total_spent DESC " +
            "LIMIT 50")
    List<Map<String, Object>> getCustomerReservationStats();
    
    /**
     * 获取月度预订统计
     */
    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m') as month, " +
            "COUNT(*) as reservation_count, " +
            "SUM(total_amount) as revenue, " +
            "AVG(total_amount) as avg_amount, " +
            "COUNT(DISTINCT customer_phone) as unique_customers " +
            "FROM reservation " +
            "WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) " +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m') " +
            "ORDER BY month DESC")
    List<Map<String, Object>> getMonthlyReservationStats();
}